package com.ltmall.api.dao.impl;

import com.ltmall.api.bean.Goods;
import com.ltmall.api.bean.HotCity;
import com.ltmall.api.bean.Search;
import com.ltmall.api.bean.ThSku;
import com.ltmall.api.dao.ISearchDAO;
import com.ltmall.api.util.PageUtil;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SearchDAO extends BaseEntityDAO<Search> implements ISearchDAO {

    @SuppressWarnings("unchecked")
    public List<HotCity> searchCity(String keyword) {
        // TODO Auto-generated method stub
        final String HQL = "from BussinessInfo info where info.province like '%" + keyword
                           + "%' or  city.city like '%" + keyword + "%' or  city.county like '%"
                           + keyword + "%'";
        @SuppressWarnings("rawtypes")
        List<HotCity> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public List<?> cityList() {
        // TODO Auto-generated method stub
        //		final String HQL = "select bInfo.companyProvince,bInfo.companyCity,bInfo.companyCounty from BussinessInfo bInfo where bInfo.companyShop = 1";
        //		final String HQL = "select bi.companyProvince, bi.companyCity, bi.companyCounty from BussinessInfo bi, Shop fs, Goods fg "
        //				+ "where bi.companyShop = 1 and bi.companyID = fs.bussinessID and fs.shopID = fg.shopID and fg.station = 1 "
        //				+ "GROUP BY bi.companyID";
        final String SQL = "select fbi.company_province, fbi.company_city, fbi.company_county "
                           + "from flavor_bussiness_info fbi, flavor_bussiness_account fbc "
                           + "where fbc.station = 1 and fbi.company_shop = 1 and fbi.company_id = fbc.company_id";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("unchecked")
    public List<Goods> searchGoods(String keyword, final PageUtil page) {
        // TODO Auto-generated method stub
        final String HQL = "from Goods goods where( goods.goodsTitle like '%" + keyword
                           + "%'  or goods.goodsName like '%" + keyword
                           + "%')and goods.station = 1 and goods.directSupplyShowType=1 ";
        @SuppressWarnings("rawtypes")
        List<Goods> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                //分页
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    public List<String> allCounty() {
        // TODO Auto-generated method stub
        final String HQL = "select distinct info.companyCounty from BussinessInfo info where info.companyCounty is not null";
        @SuppressWarnings("unchecked")
        List<String> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public List<String> allCityWithoutCounty() {
        // TODO Auto-generated method stub
        final String HQL = "select distinct info.companyCity from BussinessInfo info where info.companyCounty is null";
        @SuppressWarnings("unchecked")
        List<String> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                return query.list();
            }
        });
        return list;
    }

    public List<ThSku> getSkusByGoodsIDs(int[] goodsIDs) {
        String sql = "select * from th_sku where GOODS_ID in (";
        String params = new String();
        for (int i : goodsIDs) {
            params += (i + ",");
        }
        params = params.substring(0, params.length() - 1);
        sql += (params + ")");
        //		query.setParameter(0, params);
        Session session = getSession();
        Query query = session.createSQLQuery(sql).addEntity(ThSku.class);
        List list = query.list();
        ArrayList<ThSku> skuList = new ArrayList<ThSku>();
        for (Object obj : list) {
            skuList.add((ThSku) obj);
        }
        return skuList;
    }
}
